//配置对应路由
let express = require('express');
let router = express.Router();
let dbHelper = require('../dbConfig/dbHelper.js');

router.get('/fgwHomeData', (req,res,next) => {
  let sql = `SELECT * FROM
              \t( SELECT addr_city, count( 1 ) AS project_count,sum(tz_sum) as ztz, sum(tz_needs) as sum_tz_needs, sum(result_jn) as sum_result_jn,sum( result_jt ) AS sum_result_jt  
              FROM t_imp_excel GROUP BY addr_city ) t 
              ORDER BY t.project_count DESC`
  let value = [];
  dbHelper(sql,value).then(result => {
    res.json({
      code: result.code,
      data: result.data,
      msg: result.msg
    })
  }).catch(error => {
    console.log(error);
  })
});

router.post('/fgwTableList', (req,res,next) => {
  let params = req.body;
  let sql = `select * from t_imp_excel where 1=1 `;
  let str = '';
  if(params.addr_city !== '') {
    sql += ` and addr_city = ? `;
  }
  if(params.one_category != '') {
    sql += ` and one_category = ? `;
  }
  if(params.project_name !== '') {
    let key = params.project_name;
    sql += ` and project_name like '%${params.project_name}%' ` ;
  }
  let value = [params.addr_city, params.one_category];
  console.log(params);
  dbHelper(sql, value).then(result => {
    res.json({
      code: result.code,
      data: result.data,
      msg: result.msg
    })
  }).catch(error => {
    console.log(error);
  })

})

router.post('/fgwTableListSum', (req,res,next) => {
  let params = req.body;
  let sql = '';
  if(params.sum_level === '盟市') {
    sql = ` select * from (SELECT * FROM (SELECT (CASE WHEN addr_city = '呼和浩特市' THEN '150100' 
              \t\t\t\tWHEN addr_city = '包头市' THEN '150200' 
              \t\t\t\tWHEN addr_city = '乌海市' THEN '150300' 
              \t\t\t\tWHEN addr_city = '赤峰市' THEN '150400' 
              \t\t\t\tWHEN addr_city = '通辽市' THEN '150500' 
              \t\t\t\tWHEN addr_city = '鄂尔多斯市' THEN '150600' 
              \t\t\t\tWHEN addr_city = '呼伦贝尔市' THEN '150700' 
              \t\t\t\tWHEN addr_city = '巴彦淖尔市' THEN '150800' 
              \t\t\t\tWHEN addr_city = '乌兰察布市' THEN '150900' 
              \t\t\t\tWHEN addr_city = '兴安盟' THEN '152200' 
              \t\t\t\tWHEN addr_city = '锡林郭勒盟' THEN '152500' 
              \t\t\t\tWHEN addr_city = '阿拉善盟' THEN '152900'  END) AS city_code,
              \t\t\taddr_city, count(1) AS project_count,
              \t\t\tround(sum(tz_sum),4) AS ztz, round(sum(tz_complete),4) as sum_tz_complete, round(sum(tz_needs),4) AS sum_tz_needs,
              round(sum(tz_get_support),4) as sum_tz_get_support, 
              \t\t\tround(sum(result_jn),4) AS sum_result_jn, round(sum(result_jt),4) AS sum_result_jt 
              \t\tFROM t_imp_excel GROUP BY addr_city  ) t  ORDER BY city_code asc, t.sum_result_jt DESC ) x where 1=1  `;
    if(params.addr_city != '') {
      sql += ` and addr_city = ? `;
    }
  } else {
    sql = ` SELECT * FROM (SELECT addr_county, (CASE WHEN addr_city = '呼和浩特市' THEN '150100' 
              \t\t\t\tWHEN addr_city = '包头市' THEN '150200' 
              \t\t\t\tWHEN addr_city = '乌海市' THEN '150300' 
              \t\t\t\tWHEN addr_city = '赤峰市' THEN '150400' 
              \t\t\t\tWHEN addr_city = '通辽市' THEN '150500' 
              \t\t\t\tWHEN addr_city = '鄂尔多斯市' THEN '150600' 
              \t\t\t\tWHEN addr_city = '呼伦贝尔市' THEN '150700' 
              \t\t\t\tWHEN addr_city = '巴彦淖尔市' THEN '150800' 
              \t\t\t\tWHEN addr_city = '乌兰察布市' THEN '150900' 
              \t\t\t\tWHEN addr_city = '兴安盟' THEN '152200' 
              \t\t\t\tWHEN addr_city = '锡林郭勒盟' THEN '152500' 
              \t\t\t\tWHEN addr_city = '阿拉善盟' THEN '152900'  END) AS city_code,
              \t\t\taddr_city, count(1) AS project_count,
              \t\t\tround(sum(tz_sum),4) AS ztz, round(sum(tz_complete),4) as sum_tz_complete, round(sum(tz_needs),4) AS sum_tz_needs,
              round(sum(tz_get_support),4) as sum_tz_get_support, 
              \t\t\tround(sum(result_jn),4) AS sum_result_jn, round(sum(result_jt),4) AS sum_result_jt 
              \t\tFROM t_imp_excel GROUP BY addr_county, addr_city  ) t  where 1=1  `;
    if(params.addr_city != '') {
      sql += ` and addr_city = ? `
    }
    sql += ` ORDER BY city_code asc, t.sum_result_jt DESC `
  }
  let value = [params.addr_city]
  dbHelper(sql,value).then(result => {
    res.json({
      code: result.code,
      data: result.data,
      msg: result.msg
    })
  }).catch(error=>{

  })

})

router.post('/inMap',(req,res,next) => {
  let params = req.body;
  let sql = ` SELECT * FROM (SELECT addr_county as name, addr_city, count(1) AS project_count,
              \t\t\tround(sum(tz_sum),4) AS ztz, round(sum(tz_complete),4) as sum_tz_complete, round(sum(tz_needs),4) AS sum_tz_needs,
              round(sum(tz_get_support),4) as sum_tz_get_support, 
              \t\t\tround(sum(result_jn),4) AS sum_result_jn, round(sum(result_jt),4) AS sum_result_jt, round(sum(result_jt),4) AS value  
              \t\tFROM t_imp_excel GROUP BY addr_county, addr_city ) t  where 1=1 `;
  if(params.addr_city != '') {
    sql += ` and addr_city = ? `
  }
  console.log(sql);
  let value = [params.addr_city]
  dbHelper(sql,value).then(result => {
    res.json({
      code: result.code,
      data: result.data,
      msg: result.msg
    })
  }).catch(error=>{
  })

})

module.exports = router;
